package edu.ncet.desktop.server.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.extjs.gxt.ui.client.data.BaseModel;

public class DBHelper {
	Connection con = null;
	Statement stmt = null;
	ResultSet rs = null;
	
	public void getConnection(String dbName, String userName, String passwordString)
	{
		String connectionUrl = "jdbc:jtds:sqlserver://localhost:1433/rudila;instance=;user=sa;password=welcome";

		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			if(con == null || con.isClosed())
				con = DriverManager.getConnection(connectionUrl);
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void insertToDB(BaseModel bindModel ,String targetedDBTable) throws SQLException {
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		
		String SQLTry = "INSERT INTO " + DBConnectionInfo.getDBTable(targetedDBTable);

		String propertyString = new String();
		String valueString = new String();
		
		for(String property : bindModel.getPropertyNames())
		{
			Object obj = bindModel.get(property);
			if(obj == null)
				continue;
			
			String value = new String();
			
			if( obj instanceof String) {
			     value = ((String)obj).equals("") ? "null" : (String)obj;
			}
			else if( obj instanceof Boolean){
				 value = ((Boolean)obj)?"Y":"N";
			}
			
			propertyString += property + ",";
			valueString += "'"+value+"'" + ",";
		}
		
		propertyString = propertyString.substring(0,propertyString.length() - 1);
		valueString = valueString.substring(0,valueString.length()-1);
		
		SQLTry += "(" + propertyString + ")" + " values(" + valueString + ");"; 
		
		stmt = con.createStatement();
		stmt.executeUpdate(SQLTry);		
		con.close();
	}
	
	public void updateDB(BaseModel bindModel ,String targetedDBTable, Integer usrId) throws SQLException{
		String SQLTry = "UPDATE " + DBConnectionInfo.getDBTable(targetedDBTable)+" set ";

		String valueString = new String();
		
		for(String property : bindModel.getPropertyNames())
		{
			Object obj = bindModel.get(property);
			String value = new String();
			
			if( obj instanceof String) {
			     value = ((String)obj).equals("") ? "null" : (String)obj;
			}
			else if( obj instanceof Boolean){
				if((Boolean)obj == null )
					value = "N";
				else
				 value = ((Boolean)obj)?"Y":"N";
			}
			else if( obj instanceof Integer){
				 value = (obj == null)?"0":Integer.toString((Integer)obj);
			}
			
			valueString = "'"+value+"'" + ",";
			SQLTry += property +"="+valueString;
		}
		
		SQLTry = SQLTry.substring(0,SQLTry.length()-1);
		SQLTry += " where usrId = '" + String.valueOf(usrId) +"';"; 
		System.out.println(SQLTry);
		stmt = con.createStatement();
		stmt.executeUpdate(SQLTry);		
	}
	
	public synchronized void updateDB(BaseModel bindModel ,String targetedDBTable) throws SQLException {
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		Integer usrId = DBConnectionInfo.USR_ID;
		updateDB(bindModel, targetedDBTable, usrId);
	}
	
	public BaseModel createLoadQuery(BaseModel bindModel, String srcDBTable, Integer usrId) throws SQLException{
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		String SQLTry = "SELECT * FROM "+DBConnectionInfo.getDBTable(srcDBTable)+" where usrId = '" + usrId.toString() +"';";
		stmt = con.createStatement();
		ResultSet res = stmt.executeQuery(SQLTry);
		try
		{
		res.next();
		for(String property : bindModel.getPropertyNames())
		{
			String value = res.getString(property);
			if(value.equals("Y"))
				bindModel.set(property, true);
			else if(value.equals("N"))
				bindModel.set(property, false);
			else
				bindModel.set(property, value);
		}
		}catch(Exception ex)
		{
			ex.printStackTrace();
			return bindModel;
		}
		return bindModel;
	}
	
	public BaseModel createLoadQuery(BaseModel bindModel ,String srcDBTable ) throws SQLException {
		Integer usrId = DBConnectionInfo.USR_ID;
		return createLoadQuery(bindModel, srcDBTable , usrId);
	}
	
	public ResultSet query(String query,String[] paramNames, String[] paramValues) {
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		for(int i=0;i<paramNames.length;i++){
			paramNames[i] = ":"+paramNames[i];
		}
		
		for(int i=0;i<paramNames.length;i++){
			query = query.replace(paramNames[i], paramValues[i]);
		}
		
		try {
			stmt = con.createStatement();
			return stmt.executeQuery(query);
		} catch (SQLException e) {
			return null;
		}
	}
	
	public ResultSet query(String query, String[] paramValues) {
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		for(int i=0;i<paramValues.length;i++){
			query = query.replace("{"+i+"}", paramValues[i]);
		}
		
		try {
			stmt = con.createStatement();
			return stmt.executeQuery(query);
		} catch (SQLException e) {
			return null;
		}
	}
	
	public ResultSet query(String query, String paramValue) {
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		String[] paramValues = new String[]{paramValue};
		for(int i=0;i<paramValues.length;i++){
			query = query.replace("{"+i+"}", paramValues[i]);
		}
		
		try {
			stmt = con.createStatement();
			return stmt.executeQuery(query);
      	} catch (SQLException e) {
      		return null;
		}
	}
	
	public Boolean insertToDBUsingNumberArgs(String tableName, String... args){
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		String query = "insert into "+ tableName +" values(";
		for(int i=0;i<args.length;i++){
			query+= "'"+args[i]+"'"+",";
		}
		query = query.substring(0,query.length()-1);
		query += ");";
		try {
			stmt = con.createStatement();
			stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		closeConnection();
		return true;
	}

	public int generateUniqueId(String tableName, String primaryKey)
	{
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		int count = 0;
		ResultSet res2 = query("select max("+primaryKey+") max from "+tableName, new String());
		try {
			while(res2.next())
				count = res2.getInt("max");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count+1;
	}
	
	public boolean updateQuery(String query){
		getConnection(DBConnectionInfo.dbName, DBConnectionInfo.user, DBConnectionInfo.password);
		int effectedRows = 0;
		try {
			stmt = con.createStatement();
			effectedRows = stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    return effectedRows != 0;
	}
	
	protected void closeConnection(){
		try {
			if(!con.isClosed())
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	@Override
	protected void finalize() throws Throwable {
		closeConnection();
	}
}
